package cn.sdnu.database.service;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.locks.ReentrantLock;

import cn.sdnu.database.controller.DbController;
import cn.sdnu.database.utils.TableGenerator;
import cn.sdnu.database.utils.Utils;

/**
 * @author Master_Joe qiaoguangtong@aliyun.com
 */
public class Select {
	private static Select select = null;
	private static ReentrantLock lock = new ReentrantLock();

	private Select() {

	}

	public static Select getInstance() {
		if (select == null) {
			lock.lock();
			try {
				if (select == null) {
					select = new Select();
				}
			} finally {
				lock.unlock();
			}
		}
		return select;
	}

	/**
	 * 处理我们的Select 功能
	 * 
	 * @param sql
	 */
	public void sqlHandle(String sql) {
		if (Use.getInstance().getDatabaseUsed() != null) {
			if (verifySelect(sql)) {
				if (sql.startsWith("select count(")) {
					selectCountTable(sql);
				} else {
					selectHandle(sql);
				}
			} else {
				System.out.println("Error: You have some syntax error, please try again");
			}
		} else {
			System.out.println("ERROR: No database selected");
		}
	}

	private void selectCountTable(String sql) {
		String tableName = "";
		String databaseName = Use.getInstance().getDatabaseUsed();
		int fromIndex = sql.indexOf("from");
		String columnVluesInput = sql.substring(7, fromIndex - 1);

		List<String> tables = Utils.getTables(databaseName);

		String substring = sql.substring(fromIndex);
		tableName = "";
		Map<String, String> queryValues = new LinkedHashMap<String, String>();
		if (substring.contains("where")) {
			int whereIndex = substring.indexOf("where");
			tableName = substring.substring(5, whereIndex - 1);
			String querys = substring.substring(whereIndex + 6, substring.length() - 2);
			String[] split = querys.split(" and ");
			String[] split2 = null;
			for (String string : split) {
				split2 = string.split(" = ");
				queryValues.put(split2[0], split2[1]);
			}
			if (tables.contains(tableName)) {
				ArrayList<String> tableContent = Utils.getTableContent(tableName, databaseName);
				int count = 0;
				for (String string : tableContent) {
					int firstLineEndIndex = string.indexOf(DbController.END_INFO);
					String firstLine = string.substring(0, firstLineEndIndex);
					String realColumns = firstLine.substring(DbController.HEADER_INFO.length(), firstLineEndIndex);
					String[] columnsNamesArray = realColumns.split(DbController.INTERVAL_INFO);
					List<String> asList = Arrays.asList(columnsNamesArray);
					List<String> arrayList = Utils.getColumns(tableName, databaseName);
					int indexOf = arrayList.indexOf(split2[0]);
					if (indexOf > 0) {
						if (asList.get(indexOf).equals(split2[1])) {
							count++;
						}
					}
				}
				List<String> headersList = new ArrayList<String>();
				headersList.add(columnVluesInput);

				List<List<String>> rowsList = new ArrayList<List<String>>();
				List<String> rowList = new ArrayList<String>();
				rowList.add(String.valueOf(count));
				rowsList.add(rowList);
				if (rowsList.size() > 0) {
					String generateTable = TableGenerator.generateTable(headersList, rowsList, 1);
					System.out.println(generateTable);
					System.out.println(rowsList.size() + " rows in set\n");
				} else {
					System.out.println("Empty set\n");
				}
			} else {
				// no this table
				System.out.println("ERROR : Table '" + databaseName + "." + tableName + "' doesn't exist");
			}

		} else {
			tableName = substring.substring(5, substring.length() - 2);
			if (tables.contains(tableName)) {
				ArrayList<String> tableContent = Utils.getTableContent(tableName, databaseName);
				String valueOf = String.valueOf(tableContent.size());

				List<String> headersList = new ArrayList<String>();
				headersList.add(columnVluesInput);
				List<List<String>> rowsList = new ArrayList<List<String>>();
				List<String> rowList = new ArrayList<String>();
				rowList.add(valueOf);
				rowsList.add(rowList);
				if (rowsList.size() > 0) {
					String generateTable = TableGenerator.generateTable(headersList, rowsList, 1);
					System.out.println(generateTable);
					System.out.println(rowsList.size() + " rows in set\n");
				} else {
					System.out.println("Empty set\n");
				}

			} else {
				// no this table
				System.out.println("ERROR : Table '" + databaseName + "." + tableName + "' doesn't exist");
			}

		}
	}

	/**
	 * 处理我们的SQL语句
	 * 
	 * @param sql
	 */
	private void selectHandle(String sql) {
		int fromIndex = sql.indexOf("from");
		String databaseName = Use.getInstance().getDatabaseUsed();
		String columnVluesInput = sql.substring(7, fromIndex - 1);
		String substring = sql.substring(fromIndex);
		String tableName = "";
		Map<String, String> queryValues = new LinkedHashMap<String, String>();
		if (substring.contains("where")) {
			int whereIndex = substring.indexOf("where");
			tableName = substring.substring(5, whereIndex - 1);
			String querys = substring.substring(whereIndex + 6, substring.length() - 2);
			String[] split = querys.split(" and ");
			for (String string : split) {
				String[] split2 = string.split(" = ");
				queryValues.put(split2[0], split2[1]);
			}
		} else {
			tableName = substring.substring(5, substring.length() - 2);
		}

		if (Utils.getTables(databaseName).contains(tableName)) {
			if (columnVluesInput.equals("*")) {

				List<String> valueLines = Utils.getTableContent(tableName, databaseName);

				List<String> headersList = new ArrayList<String>();
				headersList = Utils.getColumns(tableName, databaseName);

				List<List<String>> rowsList = new ArrayList<List<String>>();

				List<String> list = new ArrayList<String>();
				List<List<String>> valuesList = new ArrayList<List<String>>();
				for (String valueLine : valueLines) {
					int firstLineEndIndex = valueLine.indexOf(DbController.END_INFO);
					String firstLine = valueLine.substring(0, firstLineEndIndex);
					String realColumns = firstLine.substring(DbController.HEADER_INFO.length(), firstLineEndIndex);
					String[] columnsNames = realColumns.split(DbController.INTERVAL_INFO);
					list = Arrays.asList(columnsNames);
					valuesList.add(list);
				}

				List<String> columnInput = headersList;
				for (int j = 0; j < valuesList.size(); j++) {
					List<String> rowList = new ArrayList<String>();

					if (!queryValues.isEmpty()) {
						for (int i = 0; i < columnInput.size(); i++) {
							if (queryValues.containsKey(columnInput.get(i))
									&& queryValues.get(columnInput.get(i)).equals(valuesList.get(j).get(i))) {

								for (int k = 0; k < columnInput.size(); k++) {
									if (valuesList.get(j).get(k).equals(DbController.NULL_VALUE)) {
										rowList.add("NULL");
									} else {
										rowList.add(valuesList.get(j).get(k));
									}
								}
								rowsList.add(rowList);
							}
						}
					} else {
						for (int i = 0; i < columnInput.size(); i++) {
							if (valuesList.get(j).get(i).equals(DbController.NULL_VALUE)) {
								rowList.add("NULL");
							} else {
								rowList.add(valuesList.get(j).get(i));
							}
						}
						rowsList.add(rowList);
					}

				}
				if (rowsList.size() > 0) {
					String generateTable = TableGenerator.generateTable(headersList, rowsList, 1);
					System.out.println(generateTable);
					System.out.println(rowsList.size() + " rows in set\n");
				} else {
					System.out.println("Empty set\n");
				}

			} else {
				String[] split = columnVluesInput.split(", ");
				List<String> columnInput = Arrays.asList(split);
				List<String> columns = Utils.getColumns(tableName, "");
				for (String str : columnInput) {
					if (!columns.contains(str)) {
						return;
					}
				}

				List<String> valueLines = Utils.getTableContent(tableName, databaseName);

				List<String> list = new ArrayList<String>();
				List<List<String>> valuesList = new ArrayList<List<String>>();
				for (String valueLine : valueLines) {
					int firstLineEndIndex = valueLine.indexOf(DbController.END_INFO);
					String firstLine = valueLine.substring(0, firstLineEndIndex);
					String realColumns = firstLine.substring(DbController.HEADER_INFO.length(), firstLineEndIndex);
					String[] columnsNames = realColumns.split(DbController.INTERVAL_INFO);
					list = Arrays.asList(columnsNames);
					valuesList.add(list);
				}

				List<List<String>> rowsList = new ArrayList<List<String>>();
				for (int j = 0; j < valuesList.size(); j++) {
					List<String> rowList = new ArrayList<String>();

					if (!queryValues.isEmpty()) {
						for (int i = 0; i < columnInput.size(); i++) {
							if (queryValues.containsKey(columnInput.get(i))
									&& queryValues.get(columnInput.get(i)).equals(valuesList.get(j).get(i))) {

								for (int k = 0; k < columnInput.size(); k++) {
									rowList.add(valuesList.get(j).get(k));
								}
								rowsList.add(rowList);
							}
						}
					} else {
						for (int i = 0; i < columnInput.size(); i++) {
							rowList.add(valuesList.get(j).get(i));
						}
						rowsList.add(rowList);
					}

				}

				List<String> headersList = columnInput;

				if (rowsList.size() > 0) {
					String generateTable = TableGenerator.generateTable(headersList, rowsList, 1);
					System.out.println(generateTable);
					System.out.println(rowsList.size() + " rows in set\n");
				} else {
					System.out.println("Empty set\n");
				}
			}

		} else {
			System.out.println("ERROR : Table '" + databaseName + "." + tableName + "' doesn't exist");
		}
	}

	/**
	 * 验证我们的SQL语句的正确性
	 * 
	 * @param sql
	 * @return
	 */
	private boolean verifySelect(String sql) {
		return true;
	}

}
